﻿CREATE PROCEDURE [maint].[BackupFullToDevice]
@DbName [sysname], @DeviceName [sysname] = NULL, @BackupSetName [sysname] = NULL, @BackupExpireDate DATETIME = NULL, @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int;
	SET @RC = 0;

	DECLARE @DeviceDoP int;
	SET @DeviceDoP = maint.GetDeviceDoP(@DbName);

	--check for ad-hoc backup
	IF (@DeviceName IS NULL)
	BEGIN
		SET @DeviceName = N'USR_' + @DbName + N'_SQL_DEVICE';

		EXEC @RC = [maint].[AddDevice] @DeviceName, @DeviceDoP, @OutputDiagnostics;
		IF(@RC <> 0) GOTO EXIT_PROC;

		SET @BackupSetName = N'USR_' + @DbName + N'_ADHOC';
		SET @BackupExpireDate = GETDATE();
	END

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID);
			PRINT N'	@DbName = N''' + ISNULL(@DbName,N'NULL') + '''';
			PRINT N'	@DeviceName = N''' + ISNULL(@DeviceName,N'NULL') + '''';
			PRINT N'	@BackupSetName = N''' + ISNULL(@BackupSetName,N'NULL') + '''';
			PRINT N'	@BackupExpireDate = N''' + ISNULL(CAST(@BackupExpireDate AS nvarchar),N'NULL') + '''';
			PRINT N'	@DeviceDoP = ' + CAST(@DeviceDoP AS nvarchar);
		END

	DECLARE @DeviceListExpr nvarchar(max) = maint.GetDeviceListExpression(@DeviceName, @DeviceDoP);

	DECLARE @FullBackupStatement nvarchar(1024);
	SET @FullBackupStatement = N'
BACKUP DATABASE [' + @DbName + '] TO ' + @DeviceListExpr + ' WITH 
DESCRIPTION = ''FULL backup for new backupset by DBA''
,EXPIREDATE = ''' + CONVERT(nchar(23),@BackupExpireDate,126) + '''
,FORMAT
,MEDIADESCRIPTION = ''SQL Backup Device''
,MEDIANAME = ''' + @DeviceName + '''
,NAME = ''' + @BackupSetName + '''
,COMPRESSION
,STATS = 10';

	IF(@OutputDiagnostics = 1)
		PRINT '@FullBackupStatement = ''' + ISNULL(@FullBackupStatement,N'NULL') + '''';

	EXEC @RC = sp_executesql @FullBackupStatement;
	
EXIT_PROC:
	RETURN(@RC);
END